package com.roc.service.impl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.BlackListBean;
import com.roc.bean.WhiteListBean;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.dao.black.IBlackDao;
import com.roc.pojo.Operators;
import com.roc.service.ServiceException;
import com.roc.service.white.IWhiteService;
import com.roc.util.Constance;
import com.roc.util.constant.SysLogType;
@Service
@Transactional
public class WhiteServiceImpl implements IWhiteService {
	static Logger logger=Logger.getLogger(WhiteServiceImpl.class);
	@Autowired
	private IBaseDao baseDao;
	@Autowired
	private ISQLBaseDao sqlBaseDao;
	@Override
	public int addWhiteList(WhiteListBean white, Operators op)
			throws ServiceException {
		String sql="INSERT INTO APSTFR.TBL_POS_WHILTE_LIST(WHILTE_NUM,WHILTE_NAME,WHILTE_USE,WHILTE_INFO,WHILTE_TYPE) VALUES(?,?,?,?,?)";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(Constance.Null2EmptyTrim(white.getMchtId()));
		parmas.add(Constance.Null2EmptyTrim(white.getMchtName()));
		parmas.add(white.getStatus());
		String remark=white.getOpr()+","+white.getDate()+","+white.getTime();
		parmas.add(remark);
		parmas.add("mcht");
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("添加【"+white.getMchtId()+"】到白名单成功!");
				baseDao.addSyslog(SysLogType.WHITE_ADD.getCode(), SysLogType.WHITE_ADD.getName(), op.getAccountNo(), op.getAccountNo()+"添加【"+white.getMchtId()+"】到白名单");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("添加白名单失败");
		}
		return effect;
	}

	@Override
	public int updateWhiteList(WhiteListBean white, Operators op)
				throws ServiceException {
		String sql="UPDATE APSTFR.TBL_POS_WHILTE_LIST SET WHILTE_NAME=?,WHILTE_USE=?,WHILTE_INFO=? WHERE WHILTE_NUM=? AND WHILTE_TYPE='mcht' ";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(Constance.Null2EmptyTrim(white.getMchtName()));
		parmas.add(Constance.Null2EmptyTrim(white.getStatus()));
		String remark=op.getAccountNo()+","+white.getDate()+","+white.getTime();
		parmas.add(remark);
		parmas.add(white.getMchtId());
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("更改白名单【"+white.getMchtId()+"】成功!");		
				baseDao.addSyslog(SysLogType.WHITE_STATUS.getCode(), SysLogType.WHITE_STATUS.getName(), op.getAccountNo(), op.getAccountNo()+"更改白名单【"+white.getMchtId()+"】成功");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("更改白名单失败");
		}
		return effect;
	}

	@Override
	public Map<String, Object> insertExportData(File file,
			Operators op) throws ServiceException {
		Map<Integer,List<Object>> uploadData=new HashMap<Integer, List<Object>>();//存放上传数据
		Map<String,Object> resultMap=new HashMap<String, Object>();//上传文件错误原因及定位
		int count=0;
		try{		
		//上传Excel文件流转换为Map集合
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel(file, uploadData,resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		//导入数据库
		logger.info("开始进行批量导入操作");
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
		String date=sdf.format(new Date());
		Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
		//检测是否重复数据
		while(it.hasNext()){
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			//循环列					
			String mchtId=Constance.Null2EmptyTrim(list.get(0));			
			WhiteListBean temp=findByWhiteNum(mchtId);
			if(temp!=null)
			{
						resultMap.put("result","导入失败,商户号【"+mchtId+"】已存在!!!");
						logger.info("停止批量导入,已存在数据【"+mchtId+"】");
						return resultMap;
			}
		}
		//开始导入数据
		it=uploadData.entrySet().iterator();
		while(it.hasNext()){
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			//循环列					
			WhiteListBean white=new WhiteListBean();
			white.setMchtId(Constance.Null2EmptyTrim(list.get(0)));
			white.setMchtName(Constance.Null2EmptyTrim(list.get(1)));
			white.setStatus(Constance.Null2EmptyTrim(list.get(2)));
			white.setOpr(op.getAccountNo());
			white.setDate(date.substring(0,8));
			white.setTime(date.substring(8));
			addWhiteList(white, op);
			count++;
			}
		}catch(Exception e){
					logger.debug(e.getMessage(), e);
					throw new ServiceException("批量导入白名单失败");
				}
		resultMap.put("result","导入成功!总计导入"+count+"条数据");
		return resultMap;
	}

	@Override
	public WhiteListBean findByWhiteNum(String mchtId) throws ServiceException {
		String sql="SELECT S.WHILTE_NUM,S.WHILTE_NAME,S.WHILTE_USE FROM APSTFR.TBL_POS_WHILTE_LIST S WHERE WHILTE_NUM=? AND WHILTE_TYPE='mcht' ";
		Object[] parmas={mchtId};
		WhiteListBean white=null;
		try {
			Object object=sqlBaseDao.findObj(sql, parmas);
			if(object!=null)
			{
				Object[] obj=(Object[]) object;
				white=new WhiteListBean();
				white.setMchtId(Constance.Null2EmptyTrim(obj[0]));
				white.setMchtName(Constance.Null2EmptyTrim(obj[1]));
				white.setStatus(Constance.Null2EmptyTrim(obj[2]));
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询【"+mchtId+"】失败");
		}
		return white;
	}
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="\\d{15}";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号不规范,只能包含数字字符且长度为15";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			String mchtName=Constance.Null2EmptyTrim(list.get(1));
			if(mchtName.length()>100||"".equals(mchtName))
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列商户名不规范,最大长度小于100且不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			String flag=Constance.Null2EmptyTrim(list.get(2));
			if(!flag.equals("0")&&!flag.equals("1"))
			{
				error_reason="第"+(entry.getKey()+1)+"行第3列状态值不规范,只能为0或1";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<3){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>3){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号","商户名","状态"};
			String titleName=null;
			for(int i=0;i<3;i++)
			{
				row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
				titleName=row.getCell(i).getStringCellValue();
				if(!titleName.equals(title[i]))
				{
					reason_error="第1行第"+(i+1)+"列的列名不正确,正确的列名为【"+title[i]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			}
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常");
			return false;
		}
		return true;
	}
}
